﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Zxb.DayWork.Logic
{
    //依赖数据中心表Uv_DomainClickSumJiaJuResult,Uv_ClickSumJiaJuCaseResult
    //如果Uv_DomainClickSumJiaJuResult中某天历史数据清除，执行程序将造成此天的点击量数据变0
    //所以绝对不能对已删除日期执行run，但可以对未删除的数据反复执行，不会造成数据不同
    public class ClickWorker : BatchWorker
    {
        private DataTable caseListClickTable;
        private DataTable caseInfoClickTable;
        private DataTable case_Estate_Table;
        private DataTable targetTable;
        private DateTime start = DateTime.Now.AddDays(-2.0);
        private DateTime end = DateTime.Now.AddDays(-1.0);

        public ClickWorker()
        {
        }

        public ClickWorker(DateTime startday, DateTime endday)
        {
            start = startday;
            end = endday;
        }

        public ClickWorker(double pastdays)
            : this(DateTime.Now.AddDays(pastdays), DateTime.Now.AddDays(-1.0))
        {

        }

        private static DataTable PrepareSchemaUdtEstateStatUpUVClick()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]
            { 
                new DataColumn("RealEstateID",typeof(long)),
                new DataColumn("StatDate",typeof(DateTime)),
                new DataColumn("DayCaseListClicks",typeof(int)),
                new DataColumn("DayCaseInfoClicks",typeof(int))
            });
            return dt;
        }

        protected override void ReadData()
        {
            //try
            //{
            //    Database sourcedb = DB.DataCenter;
            //    string sql = string.Format("select d.projcode,c.StatisticsDate,sum(c.clickSumNum) as DayCaseListClicks from Uv_DomainClickSumJiaJuResult c inner join House_domain d on c.domainid=d.id and c.statisticsDate>='{0}' and statisticsDate<'{1}' group by c.StatisticsDate,d.projcode order by c.StatisticsDate,d.projcode", start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));
            //    DbCommand dbCommand = sourcedb.GetSqlStringCommand(sql);
            //    sourceTable = sourcedb.ExecuteDataSet(dbCommand).Tables[0];
            //}
            //catch (Exception ex)
            //{
            //    Logger.InfoLog.Error(string.Concat("读取数据中心表Uv_DomainClickSumJiaJuResult出错：", ex.Message));
            //}

            string sql = string.Format("select d.projcode,c.StatisticsDate,sum(c.clickSumNum) as DayCaseListClicks from Uv_DomainClickSumJiaJuResult c with(nolock) inner join House_domain d with(nolock) on c.domainid=d.id and c.statisticsDate>='{0}' and statisticsDate<'{1}' group by c.StatisticsDate,d.projcode order by c.StatisticsDate,d.projcode", start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));
            caseListClickTable = DAL.ReadDt_Retry_ReturnNewIfNull(DB.DataCenter, sql);

            sql = string.Format("select CaseCode,StatisticsDate,ClickSumNum from Uv_ClickSumJiaJuCaseResult with(nolock) where statisticsDate>='{0}' and statisticsDate<'{1}'", start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));
            caseInfoClickTable = DAL.ReadDt_Retry_ReturnNewIfNull(DB.DataCenter, sql);

            sql = "select CaseID,RealEstateID from Cases with(nolock) where RealEstateID>0 ";
            case_Estate_Table = DAL.ReadDt_Retry_ReturnNewIfNull(DB.TaskReader, sql);
        }

        protected override void ProcessData()
        {
            targetTable = PrepareSchemaUdtEstateStatUpUVClick();

            if (caseListClickTable.Rows.Count == 0 && caseInfoClickTable.Rows.Count == 0)
            {
                return;
            }

            var query_Estate_CaseInfoClick
                = from c in caseInfoClickTable.AsEnumerable()
                  join e in case_Estate_Table.AsEnumerable()
                  on Convert.ToInt32(c.Field<object>("CaseCode")) equals e.Field<int>("CaseID") into ec
                  from item in ec.DefaultIfEmpty()
                  select new
                  {
                      CaseID = Convert.ToInt32(c.Field<object>("CaseCode")),
                      StatDate = c.Field<DateTime>("StatisticsDate").ToString("yyyy-MM-dd 00:00"),
                      RealEstateID = (item == null) ? 0 : item.Field<long>("RealEstateID"),
                      Clicks_Count = c.Field<int>("ClickSumNum")
                  };
            var query_Estate_Day_CaseInfoClick
                = from c in query_Estate_CaseInfoClick
                  group c by new { c.RealEstateID, c.StatDate } into g
                  select new
                  {
                      g.Key,
                      InfoClicks = g.Sum(c => c.Clicks_Count)
                  };
            var query_caseListClickTable
                = from c in caseListClickTable.AsEnumerable()
                  select new
                  {
                      RealEstateID = Convert.ToInt64(c.Field<object>("projcode")),
                      StatDate = c.Field<DateTime>("StatisticsDate").ToString("yyyy-MM-dd 00:00"),
                      DayCaseListClicks = c.Field<int>("DayCaseListClicks")
                  };
            var query_Estate_Day_CaseListClick
                = from c in query_caseListClickTable
                  group c by new { c.RealEstateID, c.StatDate } into g
                  select new
                  {
                      g.Key,
                      ListClicks = g.Sum(c => c.DayCaseListClicks)
                  };

            //int ss = query_Estate_Day_CaseListClick.Count();

            var query_Estate_Day_LeftJoin
                = from lc in query_Estate_Day_CaseListClick
                  join ic in query_Estate_Day_CaseInfoClick
                  on lc.Key equals ic.Key into e
                  from item in e.DefaultIfEmpty()
                  select new
                  {
                      RealEstateID = lc.Key.RealEstateID,
                      StatDate = lc.Key.StatDate,
                      DayCaseListClicks = lc.ListClicks,
                      DayCaseInfoClicks = (item == null) ? 0 : item.InfoClicks
                  };

            //int ii = query_Estate_Day_LeftJoin.Count();

            var query_Estate_Day_RightJoin
                = from ic in query_Estate_Day_CaseInfoClick
                  join lc in query_Estate_Day_CaseListClick
                  on ic.Key equals lc.Key into e
                  from item in e.DefaultIfEmpty()
                  select new
                  {
                      RealEstateID = ic.Key.RealEstateID,
                      StatDate = ic.Key.StatDate,
                      DayCaseListClicks = (item == null) ? 0 : item.ListClicks,
                      DayCaseInfoClicks = ic.InfoClicks
                  };

            //int ssss = query_Estate_Day_RightJoin.Count();

            var query_Estate_Day_ListClick_InfoClick
                = query_Estate_Day_LeftJoin.Union(query_Estate_Day_RightJoin);

            //int ssssss = query_Estate_Day_ListClick_InfoClick.Count();

            foreach (var item in query_Estate_Day_ListClick_InfoClick)
            {
                if (item.RealEstateID == 0L)
                {
                    continue;
                }
                DataRow row = targetTable.NewRow();
                row["RealEstateID"] = item.RealEstateID;
                row["StatDate"] = item.StatDate;
                row["DayCaseListClicks"] = item.DayCaseListClicks;
                row["DayCaseInfoClicks"] = item.DayCaseInfoClicks;
                targetTable.Rows.Add(row);
            }

            //foreach (DataRow dr in caseListClickTable.Rows)
            //{
            //    try
            //    {
            //        DataRow row = targetTable.NewRow();
            //        row["RealEstateID"] = Convert.ToInt64(dr["projcode"]);
            //        row["StatDate"] = Convert.ToDateTime(dr["StatisticsDate"]);
            //        row["DayCaseListClicks"] = Convert.ToInt32(dr["DayCaseListClicks"]);
            //        row["DayCaseInfoClicks"] = 0;
            //        //row["DayCaseInfoClicks"] = (int)dr["DayCaseInfoClicks"];
            //        targetTable.Rows.Add(row);
            //    }
            //    catch (Exception ex)
            //    {
            //        Logger.InfoLog.Error(string.Concat("数据中心表Uv_DomainClickSumJiaJuResult数据转换出错：", ex.Message));
            //    }
            //}
        }

        protected override void WriteData()
        {
            DAL.TVPUpdate(targetTable, "EstateStatUpClick", "UdtEstateStatClick");
        }
    }
}
